import io
import os
import random
import sqlite3
import string
import sys
import time




from markupsafe import escape
import pandas as pd
import click
from click import confirmation_option
from flask import Flask, request, flash, redirect, Response, session, jsonify
from flask import Flask, render_template
from flask import url_for
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine, inspect, column
from flask_paginate import Pagination, get_page_parameter
from sqlalchemy import false, inspect
from sqlalchemy.testing.plugin.plugin_base import engines
from sqlalchemy.util.compat import inspect_formatargspec
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import LoginManager, current_user
from flask_login import UserMixin
from flask_login import login_user
from flask_login import login_required,logout_user
from PIL import Image, ImageDraw, ImageFont


WIN = sys.platform.startswith('win')
if WIN:  # 如果是 Windows 系统，使用三个斜线
    prefix = 'sqlite:///'
else:  # 否则使用四个斜线
    prefix = 'sqlite:////'
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = prefix  + os.path.join(app.root_path, 'data.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'dev'  # 等同于 app.secret_key = 'dev'
db = SQLAlchemy(app)


login_manager = LoginManager(app)
login_manager.login_view = 'login'
@login_manager.user_loader
def load_user(user_id):
    user = User.query.get(int(user_id))
    return user


class User(db.Model,UserMixin):  # 表名将会是 user（自动生成，小写处理）
    id = db.Column(db.Integer, primary_key=True)  # 主键
    name = db.Column(db.String(20))  # 名字
    username = db.Column(db.String(20))
    password_hash = db.Column(db.String(128))

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
    def validate_password(self, password):
        return check_password_hash(self.password_hash, password)

class Movie(db.Model):  # 表名将会是 movie
    id = db.Column(db.Integer, primary_key=True)  # 主键
    title = db.Column(db.String(60))  # 电影标题
    year = db.Column(db.String(4))  # 电影年份

@app.cli.command()
@click.option('--username', prompt=True, help='The username used to login.')
@click.option('--password', prompt=True, hide_input=True,confirmation_prompt=True)
def admin(username, password):
    db.create_all()

    user =User.query.first()
    if user is not None:
        click.echo('Updating user...')
        user.username = username
        user.set_password(password)
    else:
        click.echo('Creating new user...')
        user = User( username=username,name='Admin')
        user.set_password(password)
        db.session.add(user)

    db.session.commit()
    click.echo('Done!')

@app.cli.command()  # 注册为命令，可以传入 name 参数来自定义命令
@click.option('--drop', is_flag=True, help='Create after drop.')  # 设置选项
def initdb(drop):
    """Initialize the database."""
    if drop:  # 判断是否输入了选项
        db.drop_all()
    db.create_all()
    click.echo('Initialized database.')  # 输出提示信息

@app.cli.command()
def forge():
    """Generate fake data."""
    db.create_all()
    name = 'Grey Li'
    movies = [
        {'title': 'My Neighbor Totoro', 'year': '1988'},
        {'title': 'Dead Poets Society', 'year': '1989'},
        {'title': 'A Perfect World', 'year': '1993'},
        {'title': 'Leon', 'year': '1994'},
        {'title': 'Mahjong', 'year': '1996'},
        {'title': 'Swallowtail Butterfly', 'year': '1996'},
        {'title': 'King of Comedy', 'year': '1999'},
        {'title': 'Devils on the Doorstep', 'year': '1999'},
        {'title': 'WALL-E', 'year': '2008'},
        {'title': 'The Pork of Music', 'year': '2012'},
    ]

    user = User(name=name)
    db.session.add(user)
    for m in movies:
        movie = Movie(title=m['title'], year=m['year'])
        db.session.add(movie)

    db.session.commit()
    click.echo('Done.')

def generate_captcha_image():
    # 定义图片大小及背景颜色
    image = Image.new('RGB', (80, 36), color=(random.randint(0,255),random.randint(0,255),random.randint(0,255)))

    # 使用系统自带字体，或指定字体文件路径
    font_path = "./arial.ttf"
    fnt = ImageFont.truetype(font_path, 20)
    d = ImageDraw.Draw(image)

    # 生成5位数的验证码文本
    captcha_text = ''.join(random.choices(string.ascii_uppercase + string.digits, k=5))
    d.text((10, 10), captcha_text, font=fnt, fill=(255, 255, 0))

    # 添加干扰线条和噪点
    for _ in range(random.randint(3, 5)):
        start = (random.randint(0, image.width), random.randint(0, image.height))
        end = (random.randint(0, image.width), random.randint(0, image.height))
        d.line([start, end], fill=(random.randint(50, 200), random.randint(50, 200), random.randint(50, 200)))

    for _ in range(100):
        xy = (random.randrange(0, image.width), random.randrange(0, image.height))
        d.point(xy, fill=(random.randint(50, 200), random.randint(50, 200), random.randint(50, 200)))

    return image, captcha_text

@app.route('/captcha')
def captcha():
    # 使用上述函数生成验证码图片
    image, captcha_text = generate_captcha_image()

    # 将验证码文本存储到session，以便之后进行验证
    session['captcha'] = captcha_text
    session['verification_code_time'] = time.time()
    buf = io.BytesIO()
    image.save(buf, format='PNG')
    buf.seek(0)
    return buf.getvalue(), 200, {
        'Content-Type': 'image/png',
        'Content-Length': str(len(buf.getvalue()))
    }

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':

        user_input_code = request.form['captcha']
        generated_code = session['captcha']

        verification_code_time = session.get('verification_code_time')
        print(verification_code_time)
        if (time.time() - verification_code_time) > 300:  # 300秒 = 5分钟
            return "验证码已过期，请重新获取"
        if user_input_code.upper() == generated_code.upper():
            # 验证验证码是否正确
            username = request.form['username']
            password = request.form['password']
            if not username or not password:
                flash('请输入用户名或密码')
                return redirect(url_for('login'))
            user = User.query.first()

            if username == user.username and user.validate_password(password):
                login_user(user)
                flash('Login success.')
                return redirect(url_for('index'))
            else:
                flash('用户名或密码输入错误，请重新输入')
        else:
            flash('验证码输入错误')  # 如果验证码不正确，返回错误消息
    return render_template('login.html')  # 显示登录页面

@app.route('/logout')
@login_required
def logout():
    logout_user()
    flash('You have been logged out.')
    return redirect(url_for('index'))

@app.context_processor
def inject_user():
    user = User.query.first()
    return dict(user=user)

@app.errorhandler(404)
def page_not_found(e):
    return render_template('404.html'), 404

@app.route('/index', methods=['GET', 'POST'])
@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':  # 判断是否是 POST 请求
        if not current_user.is_authenticated:
            flash('未登录无法添加.')
            return redirect(url_for('index'))
        # 获取表单数据
        title = request.form.get('title')  # 传入表单对应输入字段的 name 值
        title = "%s" % escape(title)
        year = request.form.get('year')
        year = "%s" % escape(year)

        # 验证数据
        if not title or not year or len(year) > 4 or len(title) > 60:
            flash('年份格式输入错误，应为xxxx')  # 显示错误提示
            return redirect(url_for('index'))  # 重定向回主页
        # 保存表单数据到数据库
        movie = Movie(title=title, year=year)  # 创建记录
        db.session.add(movie)  # 添加到数据库会话
        db.session.commit()  # 提交数据库会话
        flash('Item created.')  # 显示成功创建的提示
        return redirect(url_for('index'))  # 重定向回主页

    movies = Movie.query.order_by(db.desc(Movie.id)).all()

    search = False
    q = request.args.get('q')
    if q:
        search = True

    # data = request.args.to_dict()
    # page = int(data.get('page', 1))
    # per_page = int(data.get('per_page', 10))
    # res = Movie.query.order_by(db.desc(Movie.id)).paginate(page=page,per_page=per_page,error_out=False)
    # return render_template('index.html', res=res, movies=movies)


    # 获取当前页码
    page = request.args.get(get_page_parameter(), type=int, default=1)

    # 每页显示的数据量
    per_page = 10

    start = (page - 1) * per_page

    end = page * per_page if len(movies) > page * per_page else len(movies)

    # paginate = Pagination(page=page, total=len(movies))

    ret = Movie.query.order_by(db.desc(Movie.id)).slice(start, end)
    # 分页处理
    pagination = Pagination(page=page, per_page=per_page,total=len(movies), search=search,css_framework='bootstrap4')
    # 获取当前页的数据
    return render_template('index.html', movie=ret, pagination=pagination,movies=movies)

@app.route('/movie/edit/<int:movie_id>', methods=['GET', 'POST'])
@login_required
def edit(movie_id):
    movie = Movie.query.get_or_404(movie_id)

    if request.method == 'POST':  # 处理编辑表单的提交请求
        title = request.form['title']
        year = request.form['year']

        if not title or not year or len(year) != 4 or len(title) > 60:
            flash('Invalid input.')
            return redirect(url_for('edit', movie_id=movie_id))  # 重定向回对应的编辑页面

        movie.title = title  # 更新标题
        movie.year = year  # 更新年份
        db.session.commit()  # 提交数据库会话
        flash('Item updated.')
        return redirect(url_for('index'))  # 重定向回主页

    return render_template('edit.html', movie=movie)  # 传入被编辑的电影记录

@app.route('/movie/delete/<int:movie_id>', methods=['POST'])
@login_required
def delete(movie_id):
    movie = Movie.query.get_or_404(movie_id)
    print(movie.id, movie.title,movie.year)
    db.session.delete(movie)
    db.session.commit()
    flash('数据已成功删除.')
    return jsonify({"status":"success","message":"数据已成功删除"}),200
    return redirect(url_for('index'))

@app.route('/settings', methods=['GET', 'POST'])
@login_required
def settings():
    if request.method == 'POST':
        name = request.form['name']

        if not name or len(name) > 20:
            flash('Invalid input.')
            return redirect(url_for('settings'))

        user = User.query.first()
        user.name = name
        db.session.commit()
        flash('Settings updated.')
        return redirect(url_for('index'))

    return render_template('settings.html')

@app.route('/upload', methods=['GET', 'POST'])
@login_required
def upload():
    if request.method == 'POST':
        # check if the post request has the file part
        if 'file' not in request.files:
            flash('No file part')
            return redirect(url_for('login'))
        file = request.files['file']
        if file.filename == '':
            flash('No selected file')
            return redirect(url_for('login'))
        if file and file.filename.endswith('.xlsx'):
            df = pd.read_excel(file, header=0)
            df = df.applymap(lambda x:x.strip() if isinstance(x, str) else x)
            existing_data = [Movie.title for Movie in Movie.query.all()]
            imported_data = df['电影名'].tolist()
            duplicates = set(existing_data) & set(imported_data)
            if duplicates:
                flash("该记录已存在在数据库中: {duplicates}".format(duplicates=duplicates))
                return redirect(url_for('index'))
            else:
                movies_data = df.to_dict(orient='records')
                try:
                    for movies in movies_data:
                        new_movie = Movie(title=movies['电影名'], year=movies['上线年份'])
                        db.session.add(new_movie)
                    db.session.commit()
                    flash( 'Movies imported successfully')
                    return redirect(url_for('index'))
                except Exception as e:
                    db.session.rollback()
                    return Response(f'Database error: {str(e)}', status=500)
        else:
            return Response('Invalid file format', status=400)
    return render_template('upload.html')

@app.route('/search', methods=['GET', 'POST'])
def search():
    if request.method == 'POST':
        query = request.form['query']
        if not query:
            flash('请输入需要搜索的电影名称')
            return redirect(url_for('search'))
        results = Movie.query.filter(Movie.title.ilike(f'%{query}%')).all()
        return render_template('search_results.html', results=results, query=query)
    return redirect(url_for('index'))

if __name__ == '__main__':
    app.run(port=7080, debug=True)